﻿#region 程序集引用
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Reflection;
#endregion

namespace Tools
{
    public class DataTableChange
    {
        /// <summary>
        /// 将DataTable数据导入到excel中
        /// </summary>
        /// <param name="data">要导入的数据</param>
        /// <param name="isColumnWritten">DataTable的列名是否要导入</param>
        /// <param name="sheetName">要导入的excel的sheet的名称</param>
        /// <param name="fileName">文件夹路径</param>
        /// <returns>导入数据行数(包含列名那一行)</returns>
        public static int DataTableToExcel(DataTable data, string sheetName, bool isColumnWritten, string fileName)
        {
            if (data == null)
            {
                throw new ArgumentNullException("data");
            }
            if (string.IsNullOrEmpty(sheetName))
            {
                throw new ArgumentNullException(sheetName);
            }
            if (string.IsNullOrEmpty(fileName))
            {
                throw new ArgumentNullException(fileName);
            }

            try
            {
                if (File.Exists(fileName))
                {
                    File.Delete(fileName);
                }
            }
            catch (Exception)
            {
            }

            IWorkbook workbook = null;
            if (fileName.IndexOf(".xlsx", StringComparison.Ordinal) > 0)
            {
                workbook = new XSSFWorkbook();
            }
            else if (fileName.IndexOf(".xls", StringComparison.Ordinal) > 0)
            {
                workbook = new HSSFWorkbook();
            }

            FileStream fs = null;
            try
            {
                fs = new FileStream(fileName, FileMode.OpenOrCreate, FileAccess.ReadWrite);
                ISheet sheet;
                if (workbook != null)
                {
                    sheet = workbook.CreateSheet(sheetName);
                }
                else
                {
                    return -1;
                }

                int j;
                int count;
                //写入DataTable的列名，写入单元格中
                if (isColumnWritten)
                {
                    var row = sheet.CreateRow(0);
                    for (j = 0; j < data.Columns.Count; ++j)
                    {
                        row.CreateCell(j).SetCellValue(data.Columns[j].ColumnName);
                    }
                    count = 1;
                }
                else
                {
                    count = 0;
                }
                //遍历循环datatable具体数据项
                int i;
                for (i = 0; i < data.Rows.Count; ++i)
                {
                    var row = sheet.CreateRow(count);
                    for (j = 0; j < data.Columns.Count; ++j)
                    {
                        row.CreateCell(j).SetCellValue(data.Rows[i][j].ToString());
                    }
                    ++count;
                }
                //将文件流写入到excel
                workbook.Write(fs);
                return count;
            }
            catch (IOException ioex)
            {
                throw new IOException(ioex.Message);
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                if (fs != null)
                {
                    fs.Close();
                }
            }
        }

        /// <summary>
        /// 将DataTable数据导入到excel中—— 一个工作簿多个工作表
        /// 注意lstdate中datatable的顺序要和lstsheetName的顺序保持一致
        /// </summary>
        /// <param name="lstdata">要导入的数据集合</param>
        /// <param name="isColumnWritten">DataTable的列名是否要导入</param>
        /// <param name="lstsheetName">要导入的excel的sheet的名称的集合</param>
        /// <param name="fileName">文件夹路径</param>
        /// <returns>导入数据行数(包含列名那一行)</returns>
        public static bool DataTableToExcel(List<DataTable> lstdata, List<string> lstsheetName, bool isColumnWritten, string fileName)
        {
            if (lstdata == null || lstdata.Count == 0)
            {
                throw new ArgumentNullException("data");
            }
            foreach (string sheetName in lstsheetName)
            {
                if (string.IsNullOrEmpty(sheetName))
                {
                    throw new ArgumentNullException(sheetName);
                }
            }
            if (string.IsNullOrEmpty(fileName))
            {
                throw new ArgumentNullException(fileName);
            }

            try
            {
                if (File.Exists(fileName))
                {
                    File.Delete(fileName);
                }
            }
            catch (Exception)
            {
            }

            IWorkbook workbook = null;
            if (fileName.IndexOf(".xlsx", StringComparison.Ordinal) > 0)
            {
                workbook = new XSSFWorkbook();
            }
            else if (fileName.IndexOf(".xls", StringComparison.Ordinal) > 0)
            {
                workbook = new HSSFWorkbook();
            }

            FileStream fs = null;
            try
            {
                fs = new FileStream(fileName, FileMode.OpenOrCreate, FileAccess.ReadWrite);
                foreach (DataTable data in lstdata)//遍历数据源集合
                {
                    foreach (string sheetName in lstsheetName)//遍历excel表名集合
                    {
                        if (lstdata.IndexOf(data) == lstsheetName.IndexOf(sheetName))//如果数据源的序号和excel表名的序号一致
                        {
                            ISheet sheet;
                            if (workbook != null)
                            {
                                sheet = workbook.CreateSheet(sheetName);
                            }
                            else
                            {
                                return false;
                            }

                            int j;
                            int count;
                            //写入DataTable的列名，写入单元格中
                            if (isColumnWritten)
                            {
                                var row = sheet.CreateRow(0);
                                for (j = 0; j < data.Columns.Count; ++j)
                                {
                                    row.CreateCell(j).SetCellValue(data.Columns[j].ColumnName);
                                }
                                count = 1;
                            }
                            else
                            {
                                count = 0;
                            }
                            //遍历循环datatable具体数据项
                            int i;
                            for (i = 0; i < data.Rows.Count; ++i)
                            {
                                var row = sheet.CreateRow(count);
                                for (j = 0; j < data.Columns.Count; ++j)
                                {
                                    row.CreateCell(j).SetCellValue(data.Rows[i][j].ToString());
                                }
                                ++count;
                            }
                        }
                    }
                }
                //将文件流写入到excel
                workbook.Write(fs);
                return true;
            }
            catch (IOException ioex)
            {
                throw new IOException(ioex.Message);
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                if (fs != null)
                {
                    fs.Close();
                }
            }
        }

        /// <summary>    
        /// 将集合类转换成DataTable    
        /// </summary>    
        /// <param name="list">集合</param>    
        /// <returns></returns>    
        public static DataTable ToDataTable(IList list)
        {
            DataTable result = new DataTable();
            if (list.Count > 0)
            {
                PropertyInfo[] propertys = list[0].GetType().GetProperties();


                foreach (PropertyInfo pi in propertys)
                {
                    result.Columns.Add(pi.Name, pi.PropertyType);
                }
                foreach (object t in list)
                {
                    ArrayList tempList = new ArrayList();

                    foreach (PropertyInfo pi in propertys)
                    {
                        object obj = pi.GetValue(t, null);
                        tempList.Add(obj);
                    }
                    object[] array = tempList.ToArray();
                    result.LoadDataRow(array, true);
                }
            }
            return result;


        }
    }
}